import sqlite3
import project
def db_connect(db_path):
    return sqlite3.connect(db_path)

def execute_query(query, db_path, values=None):
    
    output_list = ""
    try :
        print "query", query
        conn = db_connect(db_path)
        cursor = conn.cursor()
        if values:
            cursor.execute(query, values) # values are inserted on the '?'. sqlite3 doc says this is safer
        else:
            cursor.execute(query)
        #print "Executing query ", query
        conn.commit()
        
        output_list = cursor.fetchall()
        
        #output
    except sqlite3.Error, e:
        print str(e)
        #raise e
    finally:
        cursor.close()
    
    return output_list

def execute_multiple(queries, db_path):
    #output_list = ""
    conn = db_connect(db_path)
    #cursor = conn.cursor()
    #conn.set_autocommit(False)
    #sql_script = """
    #START TRANSACTION;
    #%s;
    #COMMIT;
    #""" % ";".join(queries) 
        #exec_queries()
    #finished = True
    while 1 :
        finished = True 
        for query in queries:
    
            try:
                conn.execute(query)
    
            except sqlite3.Error, e:
            #    
                print str(e)
                print "removing query %s and restarting" % query
                conn.rollback()
                queries.remove(query)
                finished = False
                
                break
        if finished:
            break
        #finished = True
        #try:
    conn.commit()
        #except monetdb.sql.Error, e: 
          
    #conn.commit()
    #cursor.close()
    #conn.set_autocommit(True)
    conn.close()
        
    return #output_list


def extract_column_names(db_path, table_name):
    
    query = """
    PRAGMA table_info(%s)
    """ % table_name 
    
    output = execute_query(query, db_path)
    
    if output:
    
        output = [x[1] for x in output[1:]] # retrieve the column name of all entries except the first (id).
    return output#execute_query(query, db_path)


def table_size(db_path, table_name):
    num_cols = 0
    num_rows = 0
    
    query = "PRAGMA table_info(%s)" % table_name  
     
    result = execute_query(query, db_path)
    
    #print result[-1][0]
    
    if result:
        num_cols = result[-1][0]
    
    query = "select count(id) from %s" % table_name
    
    result = execute_query(query, db_path)
    
    if result: 
        num_rows = result[0][0]
    
    #print num_rows, num_cols
    
    return num_rows, num_cols 
    
    
def close_db(db_path, kill=True):
    """
    not needed for sqlite. Only here to satisfy interface
    """

def start_db(db_path):
    """
    not needed for sqlite. Only here to satisfy interface
    """
    msg = "DB started"
    
    return True, msg
